package com.googlecode.bip.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.naming.NamingException;
import org.apache.log4j.Logger;
import com.googlecode.bip.entity.IQuestion;
import com.googlecode.bip.entity.Question;
import cx.ath.choisnet.sql.SimpleQuery;
import cx.ath.choisnet.sql.SimpleUpdate;

/**
 *
 * @author Claude CHOISNET
 */
public class QuestionDAO
    extends BipDAO
        implements Listable<IQuestion>
{
    private static final long serialVersionUID = 1L;
    private final static transient Logger slogger = Logger.getLogger( QuestionDAO.class );

    /**
     * Build QuestionDAO
     */
    public QuestionDAO()
    {
    }

    @Override //Listable<IQuestion>
    public List<IQuestion> getList( String sqlQuery ) throws SQLException, NamingException
    {
        return DBHelper.getList( this, getDataSource(), sqlQuery );
    }

    @Override //Listable<IQuestion>
    public IQuestion newInstance(final ResultSet rSet )
        throws SQLException
    {
        return new Question().set( rSet );
    }

    /**
    *
    * @param idquestion
    * @param idlocal
    * @return
    * @throws NamingException
    * @throws SQLException
    */
   public IQuestion findQuestion( int idquestion, String idlocal )
       throws NamingException, SQLException
   {
       final String sql = "SELECT * FROM `questionsview` WHERE `idquestion`="
           + idquestion
           + " AND `idlocal`='"
           + idlocal
           +"';";

       slogger.debug( "findQuestion: " + sql );

       final SimpleQuery query = new SimpleQuery( getDataSource() );

       try {
           ResultSet   rSet  = query.executeQuery( sql );
           boolean     found = rSet.first();

           if( found ) {
               return newInstance( rSet );
               }
           }
       finally {
           SimpleQuery.quietClose( query );
           }

       return null;
   }

   /**
    *
    * @param idquestion
    * @return
    * @throws SQLException
    * @throws NamingException
    */
   public List<IQuestion> findQuestion( int idquestion )
       throws SQLException, NamingException
   {
       final String sql = "SELECT * FROM `questionsview` WHERE `idquestion`="
           + idquestion
           + ";";

       return getList( sql );
   }

   /**
    * Insert a new Question (must not exist)
    * @param iquestion
    * @return
    * @throws NamingException
    * @throws SQLException
    */
   public boolean insertQuestion( final IQuestion iquestion )
       throws NamingException, SQLException
   {
       Question question = Question.class.cast( iquestion );
       final String sql = "CALL add_question( '"
           + question.getIdLocal()
           + "', '"
           + question.getName()
           + "', '"
           + question.getDescription()
           + "', "
           + question.getOrder()
           + ", "
           + question.getParentIdTopic()
           + ", "
           + question.getMinValue()
           + ", "
           + question.getMaxValue()
           + ", "
           + question.getDefaultValue()
           + " );";

       final SimpleQuery query = new SimpleQuery( getDataSource() );

       slogger.info( "insertQuestion: " + sql );

       try {
           ResultSet rSet = query.executeQuery( sql );

           if( rSet.first() ) {
               question.set( rSet );

               return true;
               }
           else {
               slogger.info( "No value in ResultSet: " + rSet );
               return false;
               }
           }
       finally {
           query.quietClose();
           }
   }

   /**
    *
    * @param idquestion
    * @return
    * @throws NamingException
    * @throws SQLException
    */
   public boolean deleteQuestion( final int idquestion )
       throws NamingException, SQLException
   {
       SimpleUpdate update = new SimpleUpdate( getDataSource() );

       final String whereClause = " WHERE `idquestion`='"
           + idquestion + "';";
       final String sql1 = "DELETE FROM `questions_local`" + whereClause;
       final String sql2 = "DELETE FROM `questions`" + whereClause;

       slogger.debug( "deleteQuestion: sql1 = " + sql1 );
       slogger.debug( "deleteQuestion: sql2 = " + sql2 );

       try {
           // TODO: improve (roll back)
           /*int x1 =*/ update.doUpdate( sql1 );
           int x2 = update.doUpdate( sql2 );

           if( /*x1 == 1 &*/ x2 == 1 ) {
               return true;
               }
           }
       finally {
           SimpleUpdate.quietClose( update );
           }

       return false;
   }

   /**
    *
    * @param iquestion
    * @return
    * @throws NamingException
    * @throws SQLException
    */
   public boolean updateQuestion( final IQuestion iquestion )
       throws NamingException, SQLException
   {
       SimpleUpdate update = new SimpleUpdate( getDataSource() );

       final String sql = "CALL update_question( "
           + iquestion.getIdQuestion()
           + ", '"
           + iquestion.getIdLocal()
           + "', '"
           + iquestion.getName()
           + "', '"
           + iquestion.getDescription()
           + "', "
           + iquestion.getOrder()
           + ", "
           + iquestion.getParentIdTopic()
           + ", "
           + iquestion.getIsActive()
           + ", "
           + iquestion.getMinValue()
           + ", "
           + iquestion.getMaxValue()
           + ", "
           + iquestion.getDefaultValue()
           + " );";

       slogger.debug( "updateQuestion: " + sql );

       try {
           /*int x =*/ update.doUpdate( sql );

//           if( x == 1 ) {
//               return true;
//               }
//           slogger.warn( "CAN NOT updateTopic: " + x );
           }
       finally {
           SimpleUpdate.quietClose( update );
           }

//       return false;
       return true;
   }

}

